package smetana.modules.io;

import com.google.inject.Inject;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.pivot.collections.ArrayList;
import org.apache.pivot.collections.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import smetana.domain.join.PriceListImportPosition;
import smetana.domain.raw.Position;
import smetana.domain.raw.PositionGroup;
import smetana.modules.business.BusinessPosition;

/**
 *
 * @author dmitry.mamonov
 */
public class ExcelImportServiceImpl implements ExcelImportService {
    @Inject
    BusinessPosition businessPosition;

    @Override
    public List<PriceListImportPosition> prepareImportPriceList(File workbookFile) throws IOException {
        InputStream workbookInput = null;
        try {
            List<PriceListImportPosition> resultList = new ArrayList<PriceListImportPosition>();
            workbookInput = new FileInputStream(workbookFile);
            HSSFWorkbook workbook = new HSSFWorkbook(workbookInput);
            HSSFSheet sheet = workbook.getSheetAt(0);
            String section = null;
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                HSSFRow row = sheet.getRow(rowIndex);
                HSSFCell cellCode = row.getCell(0);
                HSSFCell cellName = row.getCell(1);
                HSSFCell cellPrice = row.getCell(6);
                if (getDouble(cellCode) == 0.0) {
                    //new section started.
                    if (cellName.getStringCellValue().trim().equals("") == false) {
                        section = cellName.getStringCellValue();
                    }
                } else {
                    //records within section.
                    if (section == null) {
                        System.out.println("IGNORE: " + cellCode.getStringCellValue());
                    } else {
                        PriceListImportPosition position = new PriceListImportPosition();
                        position.setExternalId(getInteger(cellCode));
                        position.setExternalProductGroup(section);
                        position.setExternalProductName(getString(cellName));
                        position.setExternalPrice(new BigDecimal(getString(cellPrice)));
                        resultList.add(position);
                    }
                }
            }


            { //post process entities
                Map<Integer, PositionGroup> productGroupMap = businessPosition.loadProductGroupsMap();
                Map<Integer, Position> productMap = businessPosition.loadProductsMap();
                for(PriceListImportPosition position:resultList){
                    Position product = productMap.get(position.getExternalId());
                    if (product!=null){
                        position.setLocalId(product.getId());
                        PositionGroup productGroup = productGroupMap.get(product.getPositionGroupId());
                        if (productGroup!=null){
                            position.setLocalProductGroup(productGroup.getGroupName());
                        }
                        position.setLocalProductName(product.getPositionName());
                        position.setLocalPrice(product.getPrice());
                    }
                }
            }
            return resultList;
        } finally {
            if (workbookInput != null) {
                workbookInput.close();
            }
        }
    }

    private String getString(HSSFCell cell) {
        if (cell == null) {
            return "";
        } else {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                return String.valueOf(cell.getNumericCellValue());
            } else {
                String result = cell.getStringCellValue();
                if (result == null) {
                    return "";
                } else {
                    return result.trim();
                }
            }
        }
    }

    private Integer getInteger(HSSFCell cell){
        String strVal = getString(cell);
        if (StringUtils.isNumeric(strVal)==true){
            return Integer.parseInt(strVal);
        } else {
            return null;
        }
    }

    private double getDouble(HSSFCell cell) {
        if (cell == null) {
            return 0.0;
        } else {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                return cell.getNumericCellValue();
            } else {
                try {
                    return Double.parseDouble(cell.getStringCellValue());
                } catch (Exception ignore) {
                    return 0.0;
                }
            }
        }
    }
}
